package com.swn.common.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.LinkedCaseInsensitiveMap;
/**
 * 功能：读取Excel文件，获取List<map>数据。支持Excel 2003-2007、Excel 2010版本
 * *******************************************************************
 * 重要方法说明：
 * readExcel(String path)：                         程序调用入口方法；
 * printExcelData(String path)：            打印Excel中数据 （测试方法）；
 * getKeyMap()                ：            表头属性，为有序map
 * *******************************************************************
 * 注意：表头和数据可以不为第0行、第0列开始，但必须为规则的矩形表格
 * @author taozhacai
 * @Date 2016-6-6  上午11:43:25

 */
public class ExcelReadUtil {
	// 保存表头
	public HashMap<Integer, String> keyMap = null;
	private static Logger logger = Logger.getLogger("ReadExcel.class");
	
	/**
	 * 读取Excel数据
	 * 注意：表头需要从第0行开始；但列可以不从第0列开始
	 * @param path
	 * @param startColNum：开始列
	 * @param endColNum：结束列
	 * @return
	 * @throws IOException List<LinkedCaseInsensitiveMap<Object>>
	 *
	 * @Author Leon
	 * @Date 2016-6-6 上午10:43:58
	 */
	public List<LinkedCaseInsensitiveMap<Object>> readExcel(String path) throws IOException {
		if (path == null || path.trim().length() == 0) {
			return null;
		} else {
			String postfix = getPostfix(path);
			if (postfix.length()!=0) {
				if ("xls".equals(postfix.toLowerCase())) {
					return readXls(path);
				} else if ("xlsx".equals(postfix.toLowerCase())) {
					return readXlsx(path);
				}
			} else {
				logger.error("不存在该扩展名表格");
			}
		}
		return null;
	}
	
	
	/**
	 * 打印Excel数据
	 * 
	 * @param path void
	 *
	 * @Author taozhaocai
	 * @Date 2016-6-6 上午11:42:38
	 */
	public static void printExcelData(String path) {
		try {
			List<LinkedCaseInsensitiveMap<Object>> list = new ExcelReadUtil().readExcel(path);
			for (int i = 0; i < list.size(); i++) {
				HashMap<String, Object> map = list.get(i);
				for(String key:map.keySet()){
					System.out.println(key + ":" + map.get(key));
				}
				System.out.println("-----------------------------");
				
			}

		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	

	/**
	 * 获取Excel表头的字段属性
	 * 
	 * @return HashMap<Integer,String>
	 *
	 * @Author taozhcai
	 * @Date 2016-6-6 上午11:40:52
	 */
	public HashMap<Integer, String> getKeyMap() {
		if(keyMap==null){
			logger.error("需要先执行解析Excel方法.");
		}
		return keyMap;
	}
	
	public static void main(String[] args) {
		String path = "d:\\2.xls";
		printExcelData(path);
	}



	/**
	 * 读取Excel 2010及以上版本
	 * 
	 * @param path
	 * @param startColNum
	 * @param endColNum
	 * @return
	 * @throws IOException
	 *             List<HashMap<String,Object>>
	 * 
	 * @Author taozhaocai
	 * @Date 2016-6-6 上午9:54:41
	 */
	public List<LinkedCaseInsensitiveMap<Object>> readXlsx(String path) throws IOException {
		InputStream is = new FileInputStream(path);
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
		
		List<LinkedCaseInsensitiveMap<Object>> list = new ArrayList<LinkedCaseInsensitiveMap<Object>>();
		// 保存表头
		keyMap = new HashMap<Integer, String>();
		// 读取sheet
		for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
			if (xssfSheet == null){
				continue;
			}
			// 第一个不为空的行
			int firstRowNum = xssfWorkbook.getSheetAt(numSheet).getFirstRowNum();
			// 最后一个不为空的行
			int lastRowNum = xssfWorkbook.getSheetAt(numSheet).getLastRowNum();
			// 读取行
			for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
				XSSFRow xssfRow = xssfSheet.getRow(rowNum);
				if (xssfRow != null) {
					// 实际有效的列（不包括空列）
					int columnNum = xssfRow.getPhysicalNumberOfCells();
					// 第一个不为空的列
					int firstCellNum = xssfRow.getFirstCellNum();
					// int lastCellNum = hssfRow.getLastCellNum();有问题，自己计算
					// 最后一个不为空的列
					int lastCellNum = firstCellNum + columnNum - 1;
					// 保存单条记录
					LinkedCaseInsensitiveMap<Object> dataMap = new LinkedCaseInsensitiveMap<Object>();
					if (rowNum == firstRowNum) {// 第一行作为表头，用作map的key.
						for (int i = firstCellNum; i <= lastCellNum; i++) {
							XSSFCell cell = xssfRow.getCell(i);
							String cellStr = String.valueOf(cell.getStringCellValue());
							keyMap.put(i, cellStr);
						}

					} else {
						for (int i = firstCellNum; i <= lastCellNum; i++) {
							XSSFCell cell = xssfRow.getCell(i);
							String cellStr = getValue(cell);
							String key = keyMap.get(i);
							dataMap.put(key, cellStr);
						}
						list.add(dataMap);
					}

				}
			}
		}
		return list;
	}

	/**
	 * 读取Excel 2003-2007版本
	 * 
	 * @param path
	 * @param startColNum
	 * @param endColNum
	 * @return
	 * @throws IOException List<LinkedCaseInsensitiveMap<Object>>
	 *
	 * @Author taozhaocai
	 * @Date 2016-6-6 上午10:42:12
	 */
	public List<LinkedCaseInsensitiveMap<Object>> readXls(String path) throws IOException {
		InputStream is = new FileInputStream(path);
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
		List<LinkedCaseInsensitiveMap<Object>> list = new ArrayList<LinkedCaseInsensitiveMap<Object>>();
		// 读取sheet
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				continue;
			}
			// 第一个不为空的行
			int firstRowNum = hssfWorkbook.getSheetAt(numSheet).getFirstRowNum();
			// 最后一个不为空的行
			int lastRowNum = hssfWorkbook.getSheetAt(numSheet).getLastRowNum();
			// 保存表头
			keyMap = new HashMap<Integer, String>();
			// 读取行
			for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				
				if (hssfRow != null) {
					// 实际有效的列（不包括空列）
					int columnNum = hssfRow.getPhysicalNumberOfCells();
					// 第一个不为空的列
					int firstCellNum = hssfRow.getFirstCellNum();
					// int lastCellNum = hssfRow.getLastCellNum();有问题，自己计算
					// 最后一个不为空的列
					int lastCellNum = firstCellNum + columnNum - 1;
					// 保存单条记录
					LinkedCaseInsensitiveMap<Object> dataMap = new LinkedCaseInsensitiveMap<Object>();
					if (rowNum == firstRowNum) {// 第一行作为表头，用作map的key.
						for (int i = firstCellNum; i <= lastCellNum; i++) {
							HSSFCell cell = hssfRow.getCell(i);
							String cellStr = String.valueOf(cell.getStringCellValue());
							keyMap.put(i, cellStr);
						}

					}else{
						for (int i = firstCellNum; i <= lastCellNum; i++) {
							HSSFCell cell = hssfRow.getCell(i);
							String cellStr = getValue(cell);
							String key = keyMap.get(i);
							dataMap.put(key, cellStr);
						}
						list.add(dataMap);
					}

					
				}
			}
		}
		return list;
	}

	/**
	 * 获取单元格值
	 * 
	 * @param xssfRow
	 * @return String
	 *
	 * @Author Leon
	 * @Date 2016-6-6 上午10:42:41
	 */
	@SuppressWarnings("static-access")
	private  String getValue(XSSFCell xssfRow) {
		if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
			return String.valueOf(xssfRow.getBooleanCellValue());
		} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
			return String.valueOf(xssfRow.getNumericCellValue());
		} else {
			return String.valueOf(xssfRow.getStringCellValue());
		}
	}

	/**
	 * 获取单元格值
	 * 
	 * @param hssfCell
	 * @return String
	 *
	 * @Author Leon
	 * @Date 2016-6-6 上午10:43:30
	 */
	@SuppressWarnings("static-access")
	private  String getValue(HSSFCell hssfCell) {
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
			return String.valueOf(hssfCell.getNumericCellValue());
		} else {
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}
	
	
	/**
	 * 获取路径扩展名
	 * 
	 * @param path
	 * @return String
	 *
	 * @Author taozhaocai
	 * @Date 2016-6-6 上午10:33:32
	 */
	public  String getPostfix(String path) {
		if (path == null || path.trim().length()==0) {
			return "";
		}
		if (path.contains(".")) {
			return path.substring(path.lastIndexOf(".") + 1,path.length());
		}
		return "";
	}


}
